﻿/* 
 * Since 2008 - 2013
 */

package com.hh.bss.sysadmin.dao.impl;

import java.util.*;

import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.*;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.sysadmin.dao.*;
import com.hh.bss.sysadmin.entity.*;
import com.hh.bss.sysadmin.service.*;
import com.hh.bss.sysadmin.vo.query.*;
import com.hh.bss.util.*;



import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class SystemUserReportConfDaoImpl extends BaseHibernateDao<SystemUserReportConf, java.lang.Integer> implements SystemUserReportConfDao<SystemUserReportConf, java.lang.Integer> {

	public Class getEntityClass() {
		return SystemUserReportConf.class;
	}

	public Page findPage(SystemUserReportConfQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		// 3700 业绩设置
		// conf1:Q1,
		// conf2:Q2,
		// conf3:Q3,
		// conf4:Q4,
		// conf5:年度指标
		// 3701 KPI设置
		// conf1:BD CALL数,
		// conf2:VISIT
		// conf3:联系人选数量
		// conf4:内部推荐,
		// conf5:顾问面试,
		// conf6:外部推荐,
		// conf7:客户面试

		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select concat(a.id,e.id) id,ifnull(f.id,0) typeId,a.id userId,a.account,e.code_name_cn typeName,e.id type,a.name,a.name_cn nameCn,b.branchCompanyId branchCompanyIdString,b.branchCompanyName,c.deptId,c.deptName,d.titleId titleIdString,d.title,a.title_name titleName,a.status,if(a.status='N','正常','锁定') statusName ");

		// sql.append("  ,if (e.id=3700,concat('Q1:',convert(f.conf1,char)),concat('BD CALL数:',convert(f.conf1,char))) conf1 ");
		// sql.append("  ,if (e.id=3700,concat('Q2:',convert(f.conf2,char)),concat('VISIT:',convert(f.conf2,char))) conf2 ");
		// sql.append("  ,if (e.id=3700,concat('Q3:',convert(f.conf3,char)),concat('联系人选数量:',convert(f.conf3,char))) conf3 ");
		// sql.append("  ,if (e.id=3700,concat('Q4:',convert(f.conf4,char)),concat('内部推荐:',convert(f.conf4,char))) conf4 ");
		// sql.append("  ,if (e.id=3700,concat('年度指标:',convert(f.conf5,char)),concat('顾问面试:',convert(f.conf5,char))) conf5 ");
		// sql.append("  ,if (e.id=3700,'',concat('外部推荐:',convert(f.conf6,char))) conf6 ");
		// sql.append("  ,if (e.id=3700,'',concat('客户面试:',convert(f.conf7,char))) conf7 ");
		sql.append("  ,f.conf1,f.conf2,f.conf3,f.conf4,f.conf5,f.conf6,f.conf7,f.conf8,f.conf9 ");
		sql.append("  from system_user a ");
		sql.append(" inner join (select id,code_name_cn from  system_dic where category='REPORTCONF') e on 1=1 ");

		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.branch_company_id,char)) separator ',') branchCompanyId,group_concat((b5.name) separator ',') branchCompanyName from system_user_title b2,system_branch_title b3,system_branch_dept b4,system_branch_company b5 where  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id=b5.id group by b2.user_id) b on a.id=b.user_id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.id,char)) separator ',') deptId,group_concat((b4.name) separator ',') deptName from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) c on a.id=c.user_id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b3.id,char)) separator ',') titleId,group_concat((b3.name) separator ',') title from system_user_title b2,system_branch_title b3 where  b2.title_id=b3.id group by b2.user_id) d on a.id=d.user_id ");

		sql.append(" left join  system_user_report_conf f  on a.account=f.account and f.type=e.id ");

		sql.append(" ) t ");
		List values = new ArrayList();

		sql.append(" where 1=1 ");
		if (isNotEmpty(query.getBranchCompanyId())) {
			// sql.append(" and  t.branchCompanyId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3,system_branch_dept b4 where b2.user_id=t.userId and  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id= ? ) ");
			values.add(query.getBranchCompanyId());
		}
		if (isNotEmpty(query.getDeptId())) {
			// sql.append(" and  t.deptId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3 where b2.user_id=t.userId and  b2.title_id=b3.id and   b3.dept_id= ? ) ");
			values.add(query.getDeptId());
		}
		if (isNotEmpty(query.getAccount())) {
			sql.append(" and  t.account = ? ");
			values.add(query.getAccount());
		}
		if (isNotEmpty(query.getStatus())) {
			sql.append(" and  t.status = ? ");
			values.add(query.getStatus());
		}
		if (isNotEmpty(query.getType())) {
			sql.append(" and  t.type = ? ");
			values.add(query.getType());
		}
		if (isNotEmpty(query.getTitle())) {
			// sql.append(" and  t.titleId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3 where b2.user_id=t.userId and b2.title_id=b3.id   and b3.name like  ? ) ");
			values.add("%" + query.getTitle() + "%");
		}
		

		if (query.getOnlineUser() != null)
			sql.append(AuthSqlUtil.authConfSQL(query.getOnlineUser().getUsername(), "t.account"));

		if(isNotEmpty(query.getSort())) {
        	sql.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql.append(" ").append(query.getDir());            	
        	}
        }
		else sql.append(" order by t.userId,t.type ");

		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());

	}

	public List findList(SystemUserReportConfQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		// 3700 业绩设置
		// conf1:Q1,
		// conf2:Q2,
		// conf3:Q3,
		// conf4:Q4,
		// conf5:年度指标
		// 3701 KPI设置
		// conf1:BD CALL数,
		// conf2:VISIT
		// conf3:联系人选数量
		// conf4:内部推荐,
		// conf5:顾问面试,
		// conf6:外部推荐,
		// conf7:客户面试

		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select concat(a.id,e.id) id,ifnull(f.id,0) typeId,a.id userId,a.account,e.code_name_cn typeName,e.id type,a.name,a.name_cn nameCn,b.branchCompanyId branchCompanyIdString,b.branchCompanyName,c.deptId,c.deptName,d.titleId titleIdString,d.title,a.title_name titleName,a.status,if(a.status='N','正常','锁定') statusName ");

		// sql.append("  ,if (e.id=3700,concat('Q1:',convert(f.conf1,char)),concat('BD CALL数:',convert(f.conf1,char))) conf1 ");
		// sql.append("  ,if (e.id=3700,concat('Q2:',convert(f.conf2,char)),concat('VISIT:',convert(f.conf2,char))) conf2 ");
		// sql.append("  ,if (e.id=3700,concat('Q3:',convert(f.conf3,char)),concat('联系人选数量:',convert(f.conf3,char))) conf3 ");
		// sql.append("  ,if (e.id=3700,concat('Q4:',convert(f.conf4,char)),concat('内部推荐:',convert(f.conf4,char))) conf4 ");
		// sql.append("  ,if (e.id=3700,concat('年度指标:',convert(f.conf5,char)),concat('顾问面试:',convert(f.conf5,char))) conf5 ");
		// sql.append("  ,if (e.id=3700,'',concat('外部推荐:',convert(f.conf6,char))) conf6 ");
		// sql.append("  ,if (e.id=3700,'',concat('客户面试:',convert(f.conf7,char))) conf7 ");
		sql.append("  ,f.conf1,f.conf2,f.conf3,f.conf4,f.conf5,f.conf6,f.conf7 ");
		sql.append("  from system_user a ");
		sql.append(" inner join (select id,code_name_cn from  system_dic where category='REPORTCONF') e on 1=1 ");

		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.branch_company_id,char)) separator ',') branchCompanyId,group_concat((b5.name) separator ',') branchCompanyName from system_user_title b2,system_branch_title b3,system_branch_dept b4,system_branch_company b5 where  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id=b5.id group by b2.user_id) b on a.id=b.user_id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.id,char)) separator ',') deptId,group_concat((b4.name) separator ',') deptName from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) c on a.id=c.user_id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b3.id,char)) separator ',') titleId,group_concat((b3.name) separator ',') title from system_user_title b2,system_branch_title b3 where  b2.title_id=b3.id group by b2.user_id) d on a.id=d.user_id ");

		sql.append(" left join  system_user_report_conf f  on a.account=f.account and f.type=e.id ");

		sql.append(" ) t ");
		List values = new ArrayList();

		sql.append(" where 1=1 ");
		if (isNotEmpty(query.getBranchCompanyId())) {
			// sql.append(" and  t.branchCompanyId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3,system_branch_dept b4 where b2.user_id=t.userId and  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id= ? ) ");
			values.add(query.getBranchCompanyId());
		}
		if (isNotEmpty(query.getDeptId())) {
			// sql.append(" and  t.deptId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3 where b2.user_id=t.userId and  b2.title_id=b3.id and   b3.dept_id= ? ) ");
			values.add(query.getDeptId());
		}
		if (isNotEmpty(query.getStatus())) {
			sql.append(" and  t.status = ? ");
			values.add(query.getStatus());
		}
		if (isNotEmpty(query.getAccount())) {
			sql.append(" and  t.account = ? ");
			values.add(query.getAccount());
		}
		if (isNotEmpty(query.getTitle())) {
			// sql.append(" and  t.titleId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3 where b2.user_id=t.userId and b2.title_id=b3.id   and b3.name like  ? ) ");
			values.add("%" + query.getTitle() + "%");
		}

		if (query.getOnlineUser() != null)
			sql.append(AuthSqlUtil.authConfSQL(query.getOnlineUser().getUsername(), "t.account"));

		sql.append(" order by t.userId,t.type ");

		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		return super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

	}

	@Override
	public Map systemUserTargetSumTotal(SystemUserReportConfQuery query) {
	
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		//sql.append(" select ifnull(f.id,0) typeId,a.id userId,a.account,e.id type,c.deptId,a.status ");
		sql.append("  select a.account at ,sum(ifnull(f.conf1,0)) conf1 ,sum(ifnull(f.conf2,0)) conf2,sum(ifnull(f.conf3,0)) conf3,sum(ifnull(f.conf4,0)) conf4 ,sum(ifnull(f.conf5,0)) conf5 ");
		sql.append("  from system_user a ");
		sql.append(" inner join (select id,code_name_cn from  system_dic where category='REPORTCONF') e on 1=1 ");

		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.branch_company_id,char)) separator ',') branchCompanyId,group_concat((b5.name) separator ',') branchCompanyName from system_user_title b2,system_branch_title b3,system_branch_dept b4,system_branch_company b5 where  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id=b5.id group by b2.user_id) b on a.id=b.user_id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.id,char)) separator ',') deptId,group_concat((b4.name) separator ',') deptName from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) c on a.id=c.user_id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b3.id,char)) separator ',') titleId,group_concat((b3.name) separator ',') title from system_user_title b2,system_branch_title b3 where  b2.title_id=b3.id group by b2.user_id) d on a.id=d.user_id ");

		sql.append(" left join  system_user_report_conf f  on a.account=f.account and f.type=e.id ");

		if (isNotEmpty(query.getBranchCompanyId())) {
			// sql.append(" and  t.branchCompanyId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3,system_branch_dept b4 where b2.user_id=a.id and  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id= ? ) ");
			values.add(query.getBranchCompanyId());
		}
		if (isNotEmpty(query.getDeptId())) {
			// sql.append(" and  t.deptId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3 where b2.user_id=a.id and  b2.title_id=b3.id and   b3.dept_id= ? ) ");
			values.add(query.getDeptId());
		}
		if (isNotEmpty(query.getAccount())) {
			sql.append(" and  a.account = ? ");
			values.add(query.getAccount());
		}
		if (isNotEmpty(query.getStatus())) {
			sql.append(" and  a.status = ? ");
			values.add(query.getStatus());
		}
		if (isNotEmpty(query.getType())) {
			sql.append(" and  e.id = ? ");
			values.add(query.getType());
		}
		if (isNotEmpty(query.getTitle())) {
			// sql.append(" and  t.titleId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3 where b2.user_id=a.id and b2.title_id=b3.id   and b3.name like  ? ) ");
			values.add("%" + query.getTitle() + "%");
		}
		sql.append(" order by a.id,e.id ");
		sql.append(" ) t ");
		

		sql.append(" where 1=1 ");
		

		if (query.getOnlineUser() != null)
			sql.append(AuthSqlUtil.authConfSQL(query.getOnlineUser().getUsername(), "t.at"));

		
		List l = super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0)
			return (Map) l.get(0);
		else
			return new HashMap();

		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		// return super.getJdbcDao().createPage(sql.toString(),values.toArray(),
		// new MapObjectRowMapper(), query.getPageSize(),
		// query.getPageNumber());

	}

}
